using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace 药品管理系统
{
    public partial class frm_Inventory : Form
    {
        private DataTable InventoryTable;
        private DataView InventoryViewByName;
        public frm_Inventory()
        {
            InitializeComponent();
            this.StartPosition = FormStartPosition.CenterScreen;                                            
            this.dgv_Inventory.AllowUserToAddRows = false;                                                    
            this.dgv_Inventory.RowHeadersVisible = false;                                                     
            this.dgv_Inventory.BackgroundColor = Color.White;                                                 
            this.dgv_Inventory.AutoSizeColumnsMode =
                DataGridViewAutoSizeColumnsMode.AllCells;
        }
        private void frm_Inventory_Load(object sender, EventArgs e)
        {

        }

        private void btn_Update_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();                                              
            sqlConnection.ConnectionString =
                "Server=KK的秘密;Database=MMS;Integrated Security=sspi";                              
            SqlCommand sqlCommand = new SqlCommand();                                                       
            sqlCommand.Connection = sqlConnection;                                                         
            sqlCommand.CommandText =                                                                        
                "UPDATE tb_inventory"
                + " SET Mno=@Mno,Mname=@Mname,Sno=@Sno,Sname=@Sname,Isize=@Isize,Idate=@Idate"
                + " WHERE Ino=@Ino;";
            sqlCommand.Parameters.Add("@Mname", SqlDbType.VarChar, 50, "Mname");                                  
            sqlCommand.Parameters.Add("@Sno", SqlDbType.Char, 4, "Sno");
            sqlCommand.Parameters.Add("@Sname", SqlDbType.VarChar, 50, "Sname");
            sqlCommand.Parameters.Add("@Isize", SqlDbType.VarChar, 50, "Isize");
            sqlCommand.Parameters.Add("@Mno", SqlDbType.Char, 4, "Mno");
            sqlCommand.Parameters.Add("@Ino", SqlDbType.Char, 4, "Ino");
            sqlCommand.Parameters.Add("@Idate", SqlDbType.VarChar, 50, "Idate");
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();                                           
            sqlDataAdapter.UpdateCommand = sqlCommand;                                                      
            DataTable inventoryTable = this.InventoryTable;                                                     
            sqlConnection.Open();                                                                          
            int rowAffected = sqlDataAdapter.Update(inventoryTable);                                         
            sqlConnection.Close();                                                                          
            MessageBox.Show("更新" + rowAffected.ToString() + "行。");                                      
        }

        private void btn_Delete_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();                                              
            sqlConnection.ConnectionString =
                "Server=KK的秘密;Database=MMS;Integrated Security=sspi";                              
            SqlCommand deleteCommand = new SqlCommand();                                                   
            deleteCommand.Connection = sqlConnection;                                                      
            deleteCommand.CommandText =                                                                    
                "DELETE tb_inventory"
                + " WHERE Ino='" + txb_Ino.Text + "';";
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();                                                                  
            sqlDataAdapter.DeleteCommand = deleteCommand;                                                 
            DataTable InventoryTable = (DataTable)this.dgv_Inventory.DataSource;                              
            sqlConnection.Open();
            int rowAffected = deleteCommand.ExecuteNonQuery();                                          
            sqlConnection.Close();                                                                       
            MessageBox.Show("删除" + rowAffected.ToString() + "行。");
            sqlConnection.Close();  
        }

        private void btn_Add_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();                                          
            sqlConnection.ConnectionString =
               "Server=KK的秘密;Database=MMS;Integrated Security=sspi";                        
            SqlCommand sqlCommand = sqlConnection.CreateCommand();                                     
            sqlCommand.CommandText =
                "INSERT tb_inventory (Ino,Mno,Mname,Sname,Isize) VALUES(@Ino,@Mno,@Mname,@Sname,@Isize);";                 
            sqlCommand.Parameters.AddWithValue("@Ino",this.txb_Ino.Text.Trim());
            sqlCommand.Parameters.AddWithValue("@Mno", this.txb_Mno.Text.Trim());
            sqlCommand.Parameters.AddWithValue("@Mname", this.txb_Mname.Text.Trim());
            sqlCommand.Parameters.AddWithValue("@Sname", this.cmb_Sname.Text.Trim());
            sqlCommand.Parameters.AddWithValue("@Isize", this.txb_Isize.Text.Trim());
            sqlConnection.Open();                                                                       
            int rowAffected = sqlCommand.ExecuteNonQuery();                                             
            sqlConnection.Close();                                                                      
            if (rowAffected == 1)                                                                       
            {
                MessageBox.Show("添加记录成功");                                                          
            }
            else                                                                                      
            {
                MessageBox.Show("添加记录失败");                                                        
            }  
        }

        private void btn_Query_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();                                           
            sqlConnection.ConnectionString =
              "Server=KK的秘密;Database=MMS;Integrated Security=sspi";                              
            SqlCommand sqlCommand = new SqlCommand();                                                      
            sqlCommand.Connection = sqlConnection;                                                          
            sqlCommand.CommandText = "SELECT * FROM tb_inventory;";                                             
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();                                           
            sqlDataAdapter.SelectCommand = sqlCommand;
            sqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;                                           
            DataTable inventoryTable = new DataTable();                                                                                                        
            sqlConnection.Open();                                                                           
            sqlDataAdapter.Fill(inventoryTable);                                                                                                       
            sqlConnection.Close();                                                                          
            this.InventoryTable = inventoryTable;
            this.InventoryViewByName = new DataView();
            this.InventoryViewByName.Table = this.InventoryTable;
            this.InventoryViewByName.Sort = "Mname ASC";                                                 
            this.dgv_Inventory.Columns.Clear();                                                                 
            this.dgv_Inventory.DataSource = inventoryTable;
            this.dgv_Inventory.Columns["Ino"].HeaderText = "库存编号";                                              
            this.dgv_Inventory.Columns["Mno"].HeaderText = "药品编号";                                              
            this.dgv_Inventory.Columns["Mname"].HeaderText = "药品名";
            this.dgv_Inventory.Columns["Sname"].HeaderText = "供应商名称";
            this.dgv_Inventory.Columns["Isize"].HeaderText = "库存数量"; 
        }

        private void 菜单ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frm_Menu a = new frm_Menu();
            a.Show();
            this.Hide();
        }

        private void 登录ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frm_Login b = new frm_Login();
            b.Show();
            this.Hide();
        }

        private void 注册ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frm_Register c = new frm_Register();
            c.Show();
            this.Hide();
        }

        private void txb_Mno_TextChanged(object sender, EventArgs e)
        {

        }

        private void btn_SearchByNo_Click(object sender, EventArgs e)
        {
            DataRow searchResultRow = this.InventoryTable.Rows.Find(this.txb_1.Text.Trim());
            DataTable searchResultTable = this.InventoryTable.Clone();
            searchResultTable.ImportRow(searchResultRow);
            this.dgv_Inventory.DataSource = searchResultTable; 
        }

        private void btn_SearchByName_Click(object sender, EventArgs e)
        {
            DataRowView[] searchResultRowViews =
             this.InventoryViewByName.FindRows(this.txb_2.Text.Trim());
            DataTable searchResultTable = this.InventoryTable.Clone();
            foreach (DataRowView dataRowView in searchResultRowViews)
            {
                searchResultTable.ImportRow(dataRowView.Row);
            }
            this.dgv_Inventory.DataSource = searchResultTable;
        }
    }
}
